{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "canadian-afghanistan",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd \n",
    "import matplotlib.pyplot as plt \n",
    "import random\n",
    "\n",
    "plt.rcParams['font.sans-serif'] = 'Microsoft YaHei'\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "%config InlineBackend.figure_format = 'svg'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "sorted-ebony",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymysql.connections.Connection at 0x189c09b57c0>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "conn = pymysql.connect(\n",
    "    host='localhost', port=3306,\n",
    "    user='root', password='123456',\n",
    "    database='pythondb2021_6', charset='utf8mb4'\n",
    ")\n",
    "conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "unlike-style",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "furnished-verification",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = 'select * from job'\n",
    "data = pd.read_sql(sql,conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "hydraulic-disability",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_id</th>\n",
       "      <th>job_name</th>\n",
       "      <th>providesalary_text</th>\n",
       "      <th>company_name</th>\n",
       "      <th>companytype_text</th>\n",
       "      <th>workarea_text</th>\n",
       "      <th>attribute_text</th>\n",
       "      <th>jobwelf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>高级数据分析师</td>\n",
       "      <td>1.3-1.7万/月</td>\n",
       "      <td>上海勤酬通信科技有限公司</td>\n",
       "      <td>创业公司</td>\n",
       "      <td>上海-嘉定区</td>\n",
       "      <td>上海-嘉定区-3-4年经验-本科-招1人</td>\n",
       "      <td>五险一金 节日福利 餐饮补贴 交通补贴 年终奖金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>数据分析工程师</td>\n",
       "      <td>2-3.5万/月</td>\n",
       "      <td>上海巨人网络科技有限公司</td>\n",
       "      <td>上市公司</td>\n",
       "      <td>上海-松江区</td>\n",
       "      <td>上海-松江区-1年经验-本科-招若干人</td>\n",
       "      <td>带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>数据分析专员</td>\n",
       "      <td>6-7千/月</td>\n",
       "      <td>广东联通通信建设有限公司</td>\n",
       "      <td>国企</td>\n",
       "      <td>广州-天河区</td>\n",
       "      <td>广州-天河区-1年经验-大专-招2人</td>\n",
       "      <td>五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>直播讲师（白云区新市 ）</td>\n",
       "      <td>0.5-1万/月</td>\n",
       "      <td>广州常达广告传媒有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>广州-白云区</td>\n",
       "      <td>广州-白云区-1年经验-中专-招2人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>用户画像大数据分析顾问</td>\n",
       "      <td>1-2万/月</td>\n",
       "      <td>深圳鼎然信息科技有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>深圳-福田区</td>\n",
       "      <td>深圳-福田区-3-4年经验-本科-招若干人</td>\n",
       "      <td>周末双休 带薪年假 五险一金 弹性工作 股票期权</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   job_id      job_name providesalary_text  company_name companytype_text  \\\n",
       "0       1       高级数据分析师         1.3-1.7万/月  上海勤酬通信科技有限公司             创业公司   \n",
       "1       2       数据分析工程师           2-3.5万/月  上海巨人网络科技有限公司             上市公司   \n",
       "2       3        数据分析专员             6-7千/月  广东联通通信建设有限公司               国企   \n",
       "3       4  直播讲师（白云区新市 ）           0.5-1万/月  广州常达广告传媒有限公司             民营公司   \n",
       "4       5   用户画像大数据分析顾问             1-2万/月  深圳鼎然信息科技有限公司             民营公司   \n",
       "\n",
       "  workarea_text         attribute_text  \\\n",
       "0        上海-嘉定区   上海-嘉定区-3-4年经验-本科-招1人   \n",
       "1        上海-松江区    上海-松江区-1年经验-本科-招若干人   \n",
       "2        广州-天河区     广州-天河区-1年经验-大专-招2人   \n",
       "3        广州-白云区     广州-白云区-1年经验-中专-招2人   \n",
       "4        深圳-福田区  深圳-福田区-3-4年经验-本科-招若干人   \n",
       "\n",
       "                                        jobwelf  \n",
       "0                      五险一金 节日福利 餐饮补贴 交通补贴 年终奖金  \n",
       "1  带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金  \n",
       "2       五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训  \n",
       "3                                                \n",
       "4                      周末双休 带薪年假 五险一金 弹性工作 股票期权  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "joint-declaration",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100000 entries, 0 to 99999\n",
      "Data columns (total 8 columns):\n",
      " #   Column              Non-Null Count   Dtype \n",
      "---  ------              --------------   ----- \n",
      " 0   job_id              100000 non-null  int64 \n",
      " 1   job_name            100000 non-null  object\n",
      " 2   providesalary_text  100000 non-null  object\n",
      " 3   company_name        100000 non-null  object\n",
      " 4   companytype_text    100000 non-null  object\n",
      " 5   workarea_text       100000 non-null  object\n",
      " 6   attribute_text      100000 non-null  object\n",
      " 7   jobwelf             100000 non-null  object\n",
      "dtypes: int64(1), object(7)\n",
      "memory usage: 6.1+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "exposed-vertex",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data[data.job_name.str.contains('数据')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "solved-helen",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_id</th>\n",
       "      <th>job_name</th>\n",
       "      <th>providesalary_text</th>\n",
       "      <th>company_name</th>\n",
       "      <th>companytype_text</th>\n",
       "      <th>workarea_text</th>\n",
       "      <th>attribute_text</th>\n",
       "      <th>jobwelf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>99978</th>\n",
       "      <td>99979</td>\n",
       "      <td>数据挖掘实习生（南昌）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>南昌</td>\n",
       "      <td>南昌-在校生/应届生-本科-招1人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99981</th>\n",
       "      <td>99982</td>\n",
       "      <td>数据挖掘实习生（济南）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>济南</td>\n",
       "      <td>济南-在校生/应届生-本科-招8人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99982</th>\n",
       "      <td>99983</td>\n",
       "      <td>数据挖掘实习生（广州）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>广州</td>\n",
       "      <td>广州-在校生/应届生-本科-招3人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99983</th>\n",
       "      <td>99984</td>\n",
       "      <td>数据挖掘实习生（银川）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>银川</td>\n",
       "      <td>银川-在校生/应届生-本科-招若干人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99988</th>\n",
       "      <td>99989</td>\n",
       "      <td>数据挖掘实习生（大连）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>大连</td>\n",
       "      <td>大连-在校生/应届生-本科-招2人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       job_id     job_name providesalary_text  company_name companytype_text  \\\n",
       "99978   99979  数据挖掘实习生（南昌）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "99981   99982  数据挖掘实习生（济南）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "99982   99983  数据挖掘实习生（广州）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "99983   99984  数据挖掘实习生（银川）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "99988   99989  数据挖掘实习生（大连）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "\n",
       "      workarea_text      attribute_text jobwelf  \n",
       "99978            南昌   南昌-在校生/应届生-本科-招1人          \n",
       "99981            济南   济南-在校生/应届生-本科-招8人          \n",
       "99982            广州   广州-在校生/应届生-本科-招3人          \n",
       "99983            银川  银川-在校生/应届生-本科-招若干人          \n",
       "99988            大连   大连-在校生/应届生-本科-招2人          "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "stainless-fellowship",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_id</th>\n",
       "      <th>job_name</th>\n",
       "      <th>providesalary_text</th>\n",
       "      <th>company_name</th>\n",
       "      <th>companytype_text</th>\n",
       "      <th>workarea_text</th>\n",
       "      <th>attribute_text</th>\n",
       "      <th>jobwelf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>高级数据分析师</td>\n",
       "      <td>1.3-1.7万/月</td>\n",
       "      <td>上海勤酬通信科技有限公司</td>\n",
       "      <td>创业公司</td>\n",
       "      <td>上海-嘉定区</td>\n",
       "      <td>上海-嘉定区-3-4年经验-本科-招1人</td>\n",
       "      <td>五险一金 节日福利 餐饮补贴 交通补贴 年终奖金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>数据分析工程师</td>\n",
       "      <td>2-3.5万/月</td>\n",
       "      <td>上海巨人网络科技有限公司</td>\n",
       "      <td>上市公司</td>\n",
       "      <td>上海-松江区</td>\n",
       "      <td>上海-松江区-1年经验-本科-招若干人</td>\n",
       "      <td>带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>数据分析专员</td>\n",
       "      <td>6-7千/月</td>\n",
       "      <td>广东联通通信建设有限公司</td>\n",
       "      <td>国企</td>\n",
       "      <td>广州-天河区</td>\n",
       "      <td>广州-天河区-1年经验-大专-招2人</td>\n",
       "      <td>五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>用户画像大数据分析顾问</td>\n",
       "      <td>1-2万/月</td>\n",
       "      <td>深圳鼎然信息科技有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>深圳-福田区</td>\n",
       "      <td>深圳-福田区-3-4年经验-本科-招若干人</td>\n",
       "      <td>周末双休 带薪年假 五险一金 弹性工作 股票期权</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>贝克总部急招数据分析师（高级专员）</td>\n",
       "      <td>6-8千/月</td>\n",
       "      <td>辽宁贝壳房地产经纪有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>沈阳-浑南区</td>\n",
       "      <td>沈阳-浑南区-1年经验-本科-招若干人</td>\n",
       "      <td>弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   job_id           job_name providesalary_text   company_name  \\\n",
       "0       1            高级数据分析师         1.3-1.7万/月   上海勤酬通信科技有限公司   \n",
       "1       2            数据分析工程师           2-3.5万/月   上海巨人网络科技有限公司   \n",
       "2       3             数据分析专员             6-7千/月   广东联通通信建设有限公司   \n",
       "4       5        用户画像大数据分析顾问             1-2万/月   深圳鼎然信息科技有限公司   \n",
       "6       7  贝克总部急招数据分析师（高级专员）             6-8千/月  辽宁贝壳房地产经纪有限公司   \n",
       "\n",
       "  companytype_text workarea_text         attribute_text  \\\n",
       "0             创业公司        上海-嘉定区   上海-嘉定区-3-4年经验-本科-招1人   \n",
       "1             上市公司        上海-松江区    上海-松江区-1年经验-本科-招若干人   \n",
       "2               国企        广州-天河区     广州-天河区-1年经验-大专-招2人   \n",
       "4             民营公司        深圳-福田区  深圳-福田区-3-4年经验-本科-招若干人   \n",
       "6             民营公司        沈阳-浑南区    沈阳-浑南区-1年经验-本科-招若干人   \n",
       "\n",
       "                                        jobwelf  \n",
       "0                      五险一金 节日福利 餐饮补贴 交通补贴 年终奖金  \n",
       "1  带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金  \n",
       "2       五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训  \n",
       "4                      周末双休 带薪年假 五险一金 弹性工作 股票期权  \n",
       "6        弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "exotic-fraction",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.reset_index(drop=True,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "general-democrat",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_id</th>\n",
       "      <th>job_name</th>\n",
       "      <th>providesalary_text</th>\n",
       "      <th>company_name</th>\n",
       "      <th>companytype_text</th>\n",
       "      <th>workarea_text</th>\n",
       "      <th>attribute_text</th>\n",
       "      <th>jobwelf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>高级数据分析师</td>\n",
       "      <td>1.3-1.7万/月</td>\n",
       "      <td>上海勤酬通信科技有限公司</td>\n",
       "      <td>创业公司</td>\n",
       "      <td>上海-嘉定区</td>\n",
       "      <td>上海-嘉定区-3-4年经验-本科-招1人</td>\n",
       "      <td>五险一金 节日福利 餐饮补贴 交通补贴 年终奖金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>数据分析工程师</td>\n",
       "      <td>2-3.5万/月</td>\n",
       "      <td>上海巨人网络科技有限公司</td>\n",
       "      <td>上市公司</td>\n",
       "      <td>上海-松江区</td>\n",
       "      <td>上海-松江区-1年经验-本科-招若干人</td>\n",
       "      <td>带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>数据分析专员</td>\n",
       "      <td>6-7千/月</td>\n",
       "      <td>广东联通通信建设有限公司</td>\n",
       "      <td>国企</td>\n",
       "      <td>广州-天河区</td>\n",
       "      <td>广州-天河区-1年经验-大专-招2人</td>\n",
       "      <td>五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>用户画像大数据分析顾问</td>\n",
       "      <td>1-2万/月</td>\n",
       "      <td>深圳鼎然信息科技有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>深圳-福田区</td>\n",
       "      <td>深圳-福田区-3-4年经验-本科-招若干人</td>\n",
       "      <td>周末双休 带薪年假 五险一金 弹性工作 股票期权</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>贝克总部急招数据分析师（高级专员）</td>\n",
       "      <td>6-8千/月</td>\n",
       "      <td>辽宁贝壳房地产经纪有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>沈阳-浑南区</td>\n",
       "      <td>沈阳-浑南区-1年经验-本科-招若干人</td>\n",
       "      <td>弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   job_id           job_name providesalary_text   company_name  \\\n",
       "0       1            高级数据分析师         1.3-1.7万/月   上海勤酬通信科技有限公司   \n",
       "1       2            数据分析工程师           2-3.5万/月   上海巨人网络科技有限公司   \n",
       "2       3             数据分析专员             6-7千/月   广东联通通信建设有限公司   \n",
       "3       5        用户画像大数据分析顾问             1-2万/月   深圳鼎然信息科技有限公司   \n",
       "4       7  贝克总部急招数据分析师（高级专员）             6-8千/月  辽宁贝壳房地产经纪有限公司   \n",
       "\n",
       "  companytype_text workarea_text         attribute_text  \\\n",
       "0             创业公司        上海-嘉定区   上海-嘉定区-3-4年经验-本科-招1人   \n",
       "1             上市公司        上海-松江区    上海-松江区-1年经验-本科-招若干人   \n",
       "2               国企        广州-天河区     广州-天河区-1年经验-大专-招2人   \n",
       "3             民营公司        深圳-福田区  深圳-福田区-3-4年经验-本科-招若干人   \n",
       "4             民营公司        沈阳-浑南区    沈阳-浑南区-1年经验-本科-招若干人   \n",
       "\n",
       "                                        jobwelf  \n",
       "0                      五险一金 节日福利 餐饮补贴 交通补贴 年终奖金  \n",
       "1  带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金  \n",
       "2       五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训  \n",
       "3                      周末双休 带薪年假 五险一金 弹性工作 股票期权  \n",
       "4        弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "frozen-yeast",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_id</th>\n",
       "      <th>job_name</th>\n",
       "      <th>providesalary_text</th>\n",
       "      <th>company_name</th>\n",
       "      <th>companytype_text</th>\n",
       "      <th>workarea_text</th>\n",
       "      <th>attribute_text</th>\n",
       "      <th>jobwelf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7192</th>\n",
       "      <td>99979</td>\n",
       "      <td>数据挖掘实习生（南昌）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>南昌</td>\n",
       "      <td>南昌-在校生/应届生-本科-招1人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7193</th>\n",
       "      <td>99982</td>\n",
       "      <td>数据挖掘实习生（济南）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>济南</td>\n",
       "      <td>济南-在校生/应届生-本科-招8人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7194</th>\n",
       "      <td>99983</td>\n",
       "      <td>数据挖掘实习生（广州）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>广州</td>\n",
       "      <td>广州-在校生/应届生-本科-招3人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7195</th>\n",
       "      <td>99984</td>\n",
       "      <td>数据挖掘实习生（银川）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>银川</td>\n",
       "      <td>银川-在校生/应届生-本科-招若干人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7196</th>\n",
       "      <td>99989</td>\n",
       "      <td>数据挖掘实习生（大连）</td>\n",
       "      <td>3-4.5千/月</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>大连</td>\n",
       "      <td>大连-在校生/应届生-本科-招2人</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      job_id     job_name providesalary_text  company_name companytype_text  \\\n",
       "7192   99979  数据挖掘实习生（南昌）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "7193   99982  数据挖掘实习生（济南）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "7194   99983  数据挖掘实习生（广州）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "7195   99984  数据挖掘实习生（银川）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "7196   99989  数据挖掘实习生（大连）           3-4.5千/月  亚信科技(中国)有限公司           外资（欧美）   \n",
       "\n",
       "     workarea_text      attribute_text jobwelf  \n",
       "7192            南昌   南昌-在校生/应届生-本科-招1人          \n",
       "7193            济南   济南-在校生/应届生-本科-招8人          \n",
       "7194            广州   广州-在校生/应届生-本科-招3人          \n",
       "7195            银川  银川-在校生/应届生-本科-招若干人          \n",
       "7196            大连   大连-在校生/应届生-本科-招2人          "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ancient-pollution",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "def salary_deal(text):\n",
    "    if '万/月' in text:\n",
    "        unit = 10000\n",
    "    elif '千/月' in text:\n",
    "        unit = 1000\n",
    "    elif '元/天' in text:\n",
    "        unit = 22\n",
    "    elif '元/小时' in text:\n",
    "        unit = 10*22\n",
    "    elif '万/年' in text:\n",
    "        unit = 1/12\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "    res = re.findall(r'(\\d+\\.*\\d*)',text)\n",
    "    res = list(map(eval,res))\n",
    "    if len(res)==1:\n",
    "        return int(res[0]*unit)\n",
    "    elif len(res)==2:\n",
    "        return int((res[0]+res[1])*unit/2)\n",
    "    else:\n",
    "        raise ValueError"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "paperback-change",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[:,'salary'] = data.providesalary_text.apply(salary_deal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "lesbian-gnome",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[:,'city'] = data.workarea_text.apply(lambda x:x.split('-')[0])\n",
    "data.drop(columns='job_id',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "creative-train",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>7197.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>10974.733222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>7515.879695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>9000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>14000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>165000.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              salary\n",
       "count    7197.000000\n",
       "mean    10974.733222\n",
       "std      7515.879695\n",
       "min         0.000000\n",
       "25%      6500.000000\n",
       "50%      9000.000000\n",
       "75%     14000.000000\n",
       "max    165000.000000"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "modified-spanish",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_name</th>\n",
       "      <th>providesalary_text</th>\n",
       "      <th>company_name</th>\n",
       "      <th>companytype_text</th>\n",
       "      <th>workarea_text</th>\n",
       "      <th>attribute_text</th>\n",
       "      <th>jobwelf</th>\n",
       "      <th>salary</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4460</th>\n",
       "      <td>大数据开发工程师</td>\n",
       "      <td>13-20万/月</td>\n",
       "      <td>苔花科迈（西安）信息技术有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>西安-高新技术产业开发区</td>\n",
       "      <td>西安-高新技术产业开发区-3-4年经验-本科-招若干人</td>\n",
       "      <td>五险一金 交通补贴 餐饮补贴 通讯补贴</td>\n",
       "      <td>165000</td>\n",
       "      <td>西安</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      job_name providesalary_text      company_name companytype_text  \\\n",
       "4460  大数据开发工程师           13-20万/月  苔花科迈（西安）信息技术有限公司             民营公司   \n",
       "\n",
       "     workarea_text               attribute_text              jobwelf  salary  \\\n",
       "4460  西安-高新技术产业开发区  西安-高新技术产业开发区-3-4年经验-本科-招若干人  五险一金 交通补贴 餐饮补贴 通讯补贴  165000   \n",
       "\n",
       "     city  \n",
       "4460   西安  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query('salary==165000')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "dated-saturday",
   "metadata": {},
   "outputs": [],
   "source": [
    "# internship = data[data.job_name.str.contains('实习')]\n",
    "# internship.reset_index(drop=True,inplace=True)\n",
    "\n",
    "# full_time_job = data[~data.job_name.str.contains('实习')]\n",
    "# full_time_job.reset_index(drop=True,inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "enabling-courage",
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = [0,1]+[i for i in range(4000,14001,2000)]+[20000,30000,40000,200000]\n",
    "temp = pd.cut(data.salary,bins,right=False)\n",
    "data.loc[:,'salary_range'] = temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "polyphonic-monkey",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_name</th>\n",
       "      <th>providesalary_text</th>\n",
       "      <th>company_name</th>\n",
       "      <th>companytype_text</th>\n",
       "      <th>workarea_text</th>\n",
       "      <th>attribute_text</th>\n",
       "      <th>jobwelf</th>\n",
       "      <th>salary</th>\n",
       "      <th>city</th>\n",
       "      <th>salary_range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>高级数据分析师</td>\n",
       "      <td>1.3-1.7万/月</td>\n",
       "      <td>上海勤酬通信科技有限公司</td>\n",
       "      <td>创业公司</td>\n",
       "      <td>上海-嘉定区</td>\n",
       "      <td>上海-嘉定区-3-4年经验-本科-招1人</td>\n",
       "      <td>五险一金 节日福利 餐饮补贴 交通补贴 年终奖金</td>\n",
       "      <td>15000</td>\n",
       "      <td>上海</td>\n",
       "      <td>[14000, 20000)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>数据分析工程师</td>\n",
       "      <td>2-3.5万/月</td>\n",
       "      <td>上海巨人网络科技有限公司</td>\n",
       "      <td>上市公司</td>\n",
       "      <td>上海-松江区</td>\n",
       "      <td>上海-松江区-1年经验-本科-招若干人</td>\n",
       "      <td>带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金</td>\n",
       "      <td>27500</td>\n",
       "      <td>上海</td>\n",
       "      <td>[20000, 30000)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>数据分析专员</td>\n",
       "      <td>6-7千/月</td>\n",
       "      <td>广东联通通信建设有限公司</td>\n",
       "      <td>国企</td>\n",
       "      <td>广州-天河区</td>\n",
       "      <td>广州-天河区-1年经验-大专-招2人</td>\n",
       "      <td>五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训</td>\n",
       "      <td>6500</td>\n",
       "      <td>广州</td>\n",
       "      <td>[6000, 8000)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>用户画像大数据分析顾问</td>\n",
       "      <td>1-2万/月</td>\n",
       "      <td>深圳鼎然信息科技有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>深圳-福田区</td>\n",
       "      <td>深圳-福田区-3-4年经验-本科-招若干人</td>\n",
       "      <td>周末双休 带薪年假 五险一金 弹性工作 股票期权</td>\n",
       "      <td>15000</td>\n",
       "      <td>深圳</td>\n",
       "      <td>[14000, 20000)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>贝克总部急招数据分析师（高级专员）</td>\n",
       "      <td>6-8千/月</td>\n",
       "      <td>辽宁贝壳房地产经纪有限公司</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>沈阳-浑南区</td>\n",
       "      <td>沈阳-浑南区-1年经验-本科-招若干人</td>\n",
       "      <td>弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金</td>\n",
       "      <td>7000</td>\n",
       "      <td>沈阳</td>\n",
       "      <td>[6000, 8000)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            job_name providesalary_text   company_name companytype_text  \\\n",
       "0            高级数据分析师         1.3-1.7万/月   上海勤酬通信科技有限公司             创业公司   \n",
       "1            数据分析工程师           2-3.5万/月   上海巨人网络科技有限公司             上市公司   \n",
       "2             数据分析专员             6-7千/月   广东联通通信建设有限公司               国企   \n",
       "3        用户画像大数据分析顾问             1-2万/月   深圳鼎然信息科技有限公司             民营公司   \n",
       "4  贝克总部急招数据分析师（高级专员）             6-8千/月  辽宁贝壳房地产经纪有限公司             民营公司   \n",
       "\n",
       "  workarea_text         attribute_text  \\\n",
       "0        上海-嘉定区   上海-嘉定区-3-4年经验-本科-招1人   \n",
       "1        上海-松江区    上海-松江区-1年经验-本科-招若干人   \n",
       "2        广州-天河区     广州-天河区-1年经验-大专-招2人   \n",
       "3        深圳-福田区  深圳-福田区-3-4年经验-本科-招若干人   \n",
       "4        沈阳-浑南区    沈阳-浑南区-1年经验-本科-招若干人   \n",
       "\n",
       "                                        jobwelf  salary city    salary_range  \n",
       "0                      五险一金 节日福利 餐饮补贴 交通补贴 年终奖金   15000   上海  [14000, 20000)  \n",
       "1  带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金   27500   上海  [20000, 30000)  \n",
       "2       五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训    6500   广州    [6000, 8000)  \n",
       "3                      周末双休 带薪年假 五险一金 弹性工作 股票期权   15000   深圳  [14000, 20000)  \n",
       "4        弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金    7000   沈阳    [6000, 8000)  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "pressed-cooper",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[:,'company_type'] = data.companytype_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "accepting-table",
   "metadata": {},
   "outputs": [],
   "source": [
    "def education_deal(text):\n",
    "    education = ['中专','大专','本科','硕士','博士','研究生']\n",
    "    for e in education:\n",
    "        if e in text:\n",
    "            return e\n",
    "    return '其它'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "advance-liberty",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.loc[:,'education'] = data.attribute_text.apply(education_deal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "pediatric-platinum",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_data =  data.iloc[:, [0,2,6,7,8,9,10,11]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "familiar-madness",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "loving-cassette",
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-36-aac626261f97>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-36-aac626261f97>\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    final_data.jobwelf.apply(lambda x: np.nan if x=='')\u001b[0m\n\u001b[1;37m                                                      ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# final_data.jobwelf.apply(lambda x: np.nan if x=='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "occupational-monaco",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\python384\\lib\\site-packages\\pandas\\core\\indexing.py:1597: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self.obj[key] = value\n",
      "d:\\python384\\lib\\site-packages\\pandas\\core\\indexing.py:1676: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self._setitem_single_column(ilocs[0], value, pi)\n"
     ]
    }
   ],
   "source": [
    "final_data.loc[:,'treatment_score'] = final_data.jobwelf.apply(lambda x: len(x.split()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "opposite-sentence",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_name</th>\n",
       "      <th>company_name</th>\n",
       "      <th>jobwelf</th>\n",
       "      <th>salary</th>\n",
       "      <th>city</th>\n",
       "      <th>salary_range</th>\n",
       "      <th>company_type</th>\n",
       "      <th>education</th>\n",
       "      <th>treatment_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>高级数据分析师</td>\n",
       "      <td>上海勤酬通信科技有限公司</td>\n",
       "      <td>五险一金 节日福利 餐饮补贴 交通补贴 年终奖金</td>\n",
       "      <td>15000</td>\n",
       "      <td>上海</td>\n",
       "      <td>[14000, 20000)</td>\n",
       "      <td>创业公司</td>\n",
       "      <td>本科</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>数据分析工程师</td>\n",
       "      <td>上海巨人网络科技有限公司</td>\n",
       "      <td>带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金</td>\n",
       "      <td>27500</td>\n",
       "      <td>上海</td>\n",
       "      <td>[20000, 30000)</td>\n",
       "      <td>上市公司</td>\n",
       "      <td>本科</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>数据分析专员</td>\n",
       "      <td>广东联通通信建设有限公司</td>\n",
       "      <td>五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训</td>\n",
       "      <td>6500</td>\n",
       "      <td>广州</td>\n",
       "      <td>[6000, 8000)</td>\n",
       "      <td>国企</td>\n",
       "      <td>大专</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>用户画像大数据分析顾问</td>\n",
       "      <td>深圳鼎然信息科技有限公司</td>\n",
       "      <td>周末双休 带薪年假 五险一金 弹性工作 股票期权</td>\n",
       "      <td>15000</td>\n",
       "      <td>深圳</td>\n",
       "      <td>[14000, 20000)</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>本科</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>贝克总部急招数据分析师（高级专员）</td>\n",
       "      <td>辽宁贝壳房地产经纪有限公司</td>\n",
       "      <td>弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金</td>\n",
       "      <td>7000</td>\n",
       "      <td>沈阳</td>\n",
       "      <td>[6000, 8000)</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>本科</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7192</th>\n",
       "      <td>数据挖掘实习生（南昌）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>南昌</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7193</th>\n",
       "      <td>数据挖掘实习生（济南）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>济南</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7194</th>\n",
       "      <td>数据挖掘实习生（广州）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>广州</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7195</th>\n",
       "      <td>数据挖掘实习生（银川）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>银川</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7196</th>\n",
       "      <td>数据挖掘实习生（大连）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>大连</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7197 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               job_name   company_name  \\\n",
       "0               高级数据分析师   上海勤酬通信科技有限公司   \n",
       "1               数据分析工程师   上海巨人网络科技有限公司   \n",
       "2                数据分析专员   广东联通通信建设有限公司   \n",
       "3           用户画像大数据分析顾问   深圳鼎然信息科技有限公司   \n",
       "4     贝克总部急招数据分析师（高级专员）  辽宁贝壳房地产经纪有限公司   \n",
       "...                 ...            ...   \n",
       "7192        数据挖掘实习生（南昌）   亚信科技(中国)有限公司   \n",
       "7193        数据挖掘实习生（济南）   亚信科技(中国)有限公司   \n",
       "7194        数据挖掘实习生（广州）   亚信科技(中国)有限公司   \n",
       "7195        数据挖掘实习生（银川）   亚信科技(中国)有限公司   \n",
       "7196        数据挖掘实习生（大连）   亚信科技(中国)有限公司   \n",
       "\n",
       "                                           jobwelf  salary city  \\\n",
       "0                         五险一金 节日福利 餐饮补贴 交通补贴 年终奖金   15000   上海   \n",
       "1     带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金   27500   上海   \n",
       "2          五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训    6500   广州   \n",
       "3                         周末双休 带薪年假 五险一金 弹性工作 股票期权   15000   深圳   \n",
       "4           弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金    7000   沈阳   \n",
       "...                                            ...     ...  ...   \n",
       "7192                                                  3750   南昌   \n",
       "7193                                                  3750   济南   \n",
       "7194                                                  3750   广州   \n",
       "7195                                                  3750   银川   \n",
       "7196                                                  3750   大连   \n",
       "\n",
       "        salary_range company_type education  treatment_score  \n",
       "0     [14000, 20000)         创业公司        本科                5  \n",
       "1     [20000, 30000)         上市公司        本科                9  \n",
       "2       [6000, 8000)           国企        大专                8  \n",
       "3     [14000, 20000)         民营公司        本科                5  \n",
       "4       [6000, 8000)         民营公司        本科                8  \n",
       "...              ...          ...       ...              ...  \n",
       "7192       [1, 4000)       外资（欧美）        本科                0  \n",
       "7193       [1, 4000)       外资（欧美）        本科                0  \n",
       "7194       [1, 4000)       外资（欧美）        本科                0  \n",
       "7195       [1, 4000)       外资（欧美）        本科                0  \n",
       "7196       [1, 4000)       外资（欧美）        本科                0  \n",
       "\n",
       "[7197 rows x 9 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "amazing-mills",
   "metadata": {},
   "outputs": [],
   "source": [
    "from provinces import PROVINCES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "danish-bloom",
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_province(x):\n",
    "    for p in PROVINCES:\n",
    "        for c in p.get('city'):\n",
    "            if x in c.get('name'):\n",
    "                return p.get('name')\n",
    "    return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "professional-object",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "北京市\n",
      "宁夏\n",
      "四川省\n"
     ]
    }
   ],
   "source": [
    "city = ['北京','银川','成都']\n",
    "for c in city:\n",
    "    print(find_province(c))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "saving-berry",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\python384\\lib\\site-packages\\pandas\\core\\indexing.py:1597: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self.obj[key] = value\n",
      "d:\\python384\\lib\\site-packages\\pandas\\core\\indexing.py:1676: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self._setitem_single_column(ilocs[0], value, pi)\n"
     ]
    }
   ],
   "source": [
    "final_data.loc[:,'provinces'] = final_data.city.apply(find_province)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "ruled-gauge",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_name</th>\n",
       "      <th>company_name</th>\n",
       "      <th>jobwelf</th>\n",
       "      <th>salary</th>\n",
       "      <th>city</th>\n",
       "      <th>salary_range</th>\n",
       "      <th>company_type</th>\n",
       "      <th>education</th>\n",
       "      <th>treatment_score</th>\n",
       "      <th>provinces</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>高级数据分析师</td>\n",
       "      <td>上海勤酬通信科技有限公司</td>\n",
       "      <td>五险一金 节日福利 餐饮补贴 交通补贴 年终奖金</td>\n",
       "      <td>15000</td>\n",
       "      <td>上海</td>\n",
       "      <td>[14000, 20000)</td>\n",
       "      <td>创业公司</td>\n",
       "      <td>本科</td>\n",
       "      <td>5</td>\n",
       "      <td>上海市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>数据分析工程师</td>\n",
       "      <td>上海巨人网络科技有限公司</td>\n",
       "      <td>带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金</td>\n",
       "      <td>27500</td>\n",
       "      <td>上海</td>\n",
       "      <td>[20000, 30000)</td>\n",
       "      <td>上市公司</td>\n",
       "      <td>本科</td>\n",
       "      <td>9</td>\n",
       "      <td>上海市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>数据分析专员</td>\n",
       "      <td>广东联通通信建设有限公司</td>\n",
       "      <td>五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训</td>\n",
       "      <td>6500</td>\n",
       "      <td>广州</td>\n",
       "      <td>[6000, 8000)</td>\n",
       "      <td>国企</td>\n",
       "      <td>大专</td>\n",
       "      <td>8</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>用户画像大数据分析顾问</td>\n",
       "      <td>深圳鼎然信息科技有限公司</td>\n",
       "      <td>周末双休 带薪年假 五险一金 弹性工作 股票期权</td>\n",
       "      <td>15000</td>\n",
       "      <td>深圳</td>\n",
       "      <td>[14000, 20000)</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>本科</td>\n",
       "      <td>5</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>贝克总部急招数据分析师（高级专员）</td>\n",
       "      <td>辽宁贝壳房地产经纪有限公司</td>\n",
       "      <td>弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金</td>\n",
       "      <td>7000</td>\n",
       "      <td>沈阳</td>\n",
       "      <td>[6000, 8000)</td>\n",
       "      <td>民营公司</td>\n",
       "      <td>本科</td>\n",
       "      <td>8</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7192</th>\n",
       "      <td>数据挖掘实习生（南昌）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>南昌</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "      <td>江西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7193</th>\n",
       "      <td>数据挖掘实习生（济南）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>济南</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "      <td>山东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7194</th>\n",
       "      <td>数据挖掘实习生（广州）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>广州</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7195</th>\n",
       "      <td>数据挖掘实习生（银川）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>银川</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "      <td>宁夏</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7196</th>\n",
       "      <td>数据挖掘实习生（大连）</td>\n",
       "      <td>亚信科技(中国)有限公司</td>\n",
       "      <td></td>\n",
       "      <td>3750</td>\n",
       "      <td>大连</td>\n",
       "      <td>[1, 4000)</td>\n",
       "      <td>外资（欧美）</td>\n",
       "      <td>本科</td>\n",
       "      <td>0</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7197 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               job_name   company_name  \\\n",
       "0               高级数据分析师   上海勤酬通信科技有限公司   \n",
       "1               数据分析工程师   上海巨人网络科技有限公司   \n",
       "2                数据分析专员   广东联通通信建设有限公司   \n",
       "3           用户画像大数据分析顾问   深圳鼎然信息科技有限公司   \n",
       "4     贝克总部急招数据分析师（高级专员）  辽宁贝壳房地产经纪有限公司   \n",
       "...                 ...            ...   \n",
       "7192        数据挖掘实习生（南昌）   亚信科技(中国)有限公司   \n",
       "7193        数据挖掘实习生（济南）   亚信科技(中国)有限公司   \n",
       "7194        数据挖掘实习生（广州）   亚信科技(中国)有限公司   \n",
       "7195        数据挖掘实习生（银川）   亚信科技(中国)有限公司   \n",
       "7196        数据挖掘实习生（大连）   亚信科技(中国)有限公司   \n",
       "\n",
       "                                           jobwelf  salary city  \\\n",
       "0                         五险一金 节日福利 餐饮补贴 交通补贴 年终奖金   15000   上海   \n",
       "1     带薪年假 五险一金 免费班车 绩效奖金 节日福利 交通补贴 餐饮补贴 通讯补贴 年终奖金   27500   上海   \n",
       "2          五险一金 通讯补贴 餐饮补贴 定期体检 绩效奖金 年终奖金 交通补贴 专业培训    6500   广州   \n",
       "3                         周末双休 带薪年假 五险一金 弹性工作 股票期权   15000   深圳   \n",
       "4           弹性工作 全勤奖 交通补贴 餐饮补贴 住房补贴 加班补贴 节日福利 五险一金    7000   沈阳   \n",
       "...                                            ...     ...  ...   \n",
       "7192                                                  3750   南昌   \n",
       "7193                                                  3750   济南   \n",
       "7194                                                  3750   广州   \n",
       "7195                                                  3750   银川   \n",
       "7196                                                  3750   大连   \n",
       "\n",
       "        salary_range company_type education  treatment_score provinces  \n",
       "0     [14000, 20000)         创业公司        本科                5       上海市  \n",
       "1     [20000, 30000)         上市公司        本科                9       上海市  \n",
       "2       [6000, 8000)           国企        大专                8       广东省  \n",
       "3     [14000, 20000)         民营公司        本科                5       广东省  \n",
       "4       [6000, 8000)         民营公司        本科                8       辽宁省  \n",
       "...              ...          ...       ...              ...       ...  \n",
       "7192       [1, 4000)       外资（欧美）        本科                0       江西省  \n",
       "7193       [1, 4000)       外资（欧美）        本科                0       山东省  \n",
       "7194       [1, 4000)       外资（欧美）        本科                0       广东省  \n",
       "7195       [1, 4000)       外资（欧美）        本科                0        宁夏  \n",
       "7196       [1, 4000)       外资（欧美）        本科                0       辽宁省  \n",
       "\n",
       "[7197 rows x 10 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "mexican-monaco",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_data.to_excel('job_data.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adaptive-income",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
